using System;
using Microsoft.SqlServer.Dts.Runtime;
using System.Data.OleDb;
using System.Xml;

namespace Excel2ConnMgrCS
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Naming", "CA1724:TypeNamesShouldNotMatchNamespaces"), DtsConnection(ConnectionType = "EXCEL2CS", DisplayName = "Excel2ConnectionManager (CS)",
    Description = "Connection manager for Excel files with support for Import Mode",
    UITypeName = "Excel2ConnMgrUICS.Excel2ConnMgrUICS,Excel2ConnMgrUICS,Version=1.0.0.0,Culture=neutral,PublicKeyToken=<insert public key token here>")]
    public class Excel2ConnMgrCS :
      ConnectionManagerBase
    {
        #region Variables & Constants

        private string _excelFile = String.Empty;
        private const bool FIRSTROWHASNAMES_DEFAULT = true;
        private bool _firstRowHasColumnNames = FIRSTROWHASNAMES_DEFAULT;
        //private const bool USEIMPORTMODE_DEFAULT = false;
        private bool _useImportMode;
        private string _connectionString = String.Empty;

        private const string CONNECTIONSTRING_TEMPLATE = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<file>;Extended Properties=\"Excel 8.0;<extprop>\"";

        #endregion

        #region  Constructors

        public Excel2ConnMgrCS()
        {

            UpdateConnectionString();

        }

        public Excel2ConnMgrCS(string excelFile)
        {

            _excelFile = excelFile;

            UpdateConnectionString();

        }

        public Excel2ConnMgrCS(string excelFile, bool hdrValue, bool imexValue)
        {

            _excelFile = excelFile;
            _firstRowHasColumnNames = hdrValue;
            _useImportMode = imexValue;

            UpdateConnectionString();

        }

        #endregion

        #region  Properties

        public string ExcelFile
        {
            get
            {
                return _excelFile;
            }
            set
            {
                _excelFile = value;
            }
        }

        public bool FirstRowHasColumnNames
        {
            get
            {
                return _firstRowHasColumnNames;
            }
            set
            {
                _firstRowHasColumnNames = value;
            }
        }

        public bool UseImportMode
        {
            get
            {
                return _useImportMode;
            }
            set
            {
                _useImportMode = value;
            }
        }

        public override string ConnectionString
        {
            get
            {
                UpdateConnectionString();
                return _connectionString;
            }
            set
            {
                _connectionString = value;
            }
        }

        #endregion

        #region  Methods

        public override object AcquireConnection(object txn)
        {

            OleDbConnection excelConnection = new OleDbConnection();

            UpdateConnectionString();

            {
                excelConnection.ConnectionString = _connectionString;
                excelConnection.Open();
            }

            return excelConnection;

        }

        public override void ReleaseConnection(object connection)
        {
            // Nothing to do.
        }

        public override Microsoft.SqlServer.Dts.Runtime.DTSExecResult Validate(Microsoft.SqlServer.Dts.Runtime.IDTSInfoEvents infoEvents)
        {

            if (String.IsNullOrEmpty(_excelFile))
            {
                infoEvents.FireError(0, "ExcelConnectionManager2", "No Excel file specified", String.Empty, 0);
                return DTSExecResult.Failure;
            }
            else
            {
                return DTSExecResult.Success;
            }

        }

        #endregion

        #region  Helper functions

        private void UpdateConnectionString()
        {

            string temporaryString = CONNECTIONSTRING_TEMPLATE;
            string extendedProperties = String.Empty;

            // Fill in Data Source property.
            temporaryString = temporaryString.Replace("<file>", _excelFile);

            // Fill in extended properties.
            if (_firstRowHasColumnNames)
            {
                extendedProperties += "HDR=Yes;";
            }
            else
            {
                extendedProperties += "HDR=No;";
            }
            if (_useImportMode)
            {
                extendedProperties += "IMEX=1;";
            }
            temporaryString = temporaryString.Replace("<extprop>", extendedProperties);

            _connectionString = temporaryString;

        }

        #endregion

    }
}
